/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pe.gob.pnp.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import pe.gob.pnp.beans.PersonalPolicialBean;
import pe.gob.pnp.factories.ConnectionPool;

/**
 *
 * @author gcarlos
 */
public class GestionPersonalPolicial {

    public List<PersonalPolicialBean> obtenerPersonalPolicial(String apepaterno, String apematerno, String nombres, String dni, String cip) throws SQLException {
        List<PersonalPolicialBean> lstPersonalPolicial = new ArrayList<PersonalPolicialBean>();
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "SELECT NPERS_IDENTIFICADOR, CPERS_APEPATERNO, CPERS_APEMATERNO,";
            sql += " CPERS_NOMBRE,BPERS_TITULAR,NPERS_TITULAR,CPERS_CODOFIN,";
            sql += " CPERS_UNIDAD,CGRAD_DESCRIPCION,CPERS_NRODNI,CPERS_NROCIP,CPERS_SEXO";
            sql += " FROM PERMA_PERSONA";
            sql += " WHERE BPERS_ELIMINADO = 0   ";
            sql += " AND UPPER(CPERS_APEPATERNO) LIKE NVL(UPPER(?),UPPER(CPERS_APEPATERNO)) || '%'";
            sql += " AND UPPER(CPERS_APEMATERNO) LIKE NVL(UPPER(?),UPPER(CPERS_APEMATERNO)) || '%'";
            sql += " AND UPPER(CPERS_NOMBRE) LIKE NVL(UPPER(?),UPPER(CPERS_NOMBRE)) || '%'";
            sql += " AND CPERS_NRODNI = NVL(UPPER(?),UPPER(CPERS_NRODNI))";
            sql += " AND CPERS_NROCIP = NVL(UPPER(?),UPPER(CPERS_NROCIP))";
            sql += " ORDER BY CPERS_APEPATERNO,CPERS_APEMATERNO,CPERS_NOMBRE";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, (apepaterno == null || apepaterno.equals("")) ? null : apepaterno);
            ps.setString(2, (apematerno == null || apematerno.equals("")) ? null : apematerno);
            ps.setString(3, (nombres == null || nombres.equals("")) ? null : nombres);
            ps.setString(4, (dni == null || dni.equals("")) ? null : dni);
            ps.setString(5, (cip == null || cip.equals("")) ? null : cip);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                
                PersonalPolicialBean personalpolicial = new PersonalPolicialBean();
                personalpolicial.setNpersIdentificador(rs.getInt("NPERS_IDENTIFICADOR"));
                personalpolicial.setCpersApepaterno(rs.getString("CPERS_APEPATERNO").toUpperCase());
                personalpolicial.setCpersApematerno(rs.getString("CPERS_APEMATERNO").toUpperCase());
                personalpolicial.setCpersNombre(rs.getString("CPERS_NOMBRE").toUpperCase());
                personalpolicial.setBpersTitular(rs.getBoolean("BPERS_TITULAR"));
                personalpolicial.setNpersTitular(rs.getInt("NPERS_TITULAR"));
                personalpolicial.setCpers_codofin(rs.getString("CPERS_CODOFIN"));
                personalpolicial.setCpers_nrocip(rs.getString("CPERS_NROCIP"));
                personalpolicial.setCpers_unidad(rs.getString("CPERS_UNIDAD"));
                personalpolicial.setCpers_nrodni(rs.getString("CPERS_NRODNI"));
                personalpolicial.setCgrad_descripcion(rs.getString("CGRAD_DESCRIPCION"));
                lstPersonalPolicial.add(personalpolicial);
            }
            rs.close();
            ps.close();
        }catch(Exception err){
            err.printStackTrace();
        }finally {
            ConnectionPool.liberarConexion(conn);
        }
        return lstPersonalPolicial;
    }
}
